Performing Two-Way Lookups Using INDEX and MATCH

Course: Advanced Excel

Topic: Performing Two-Way Lookups Using INDEX and MATCH

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to Two-Way Lookups

A two-way lookup is an Excel technique used to retrieve a value from a table by identifying both a row and a column. It is especially useful when data is arranged in a matrix, where categories appear vertically and periods, products, departments, or other headings appear horizontally.

The combination of INDEX and MATCH makes two-way lookups flexible and efficient. The MATCH function identifies the position of the required row and column, while INDEX returns the value located at their intersection.

For example, a sales manager may need to find the sales generated by a particular employee during a particular month. Instead of manually searching a large worksheet, a two-way lookup can retrieve the required amount automatically.

Two-way lookups are commonly used in:

• Monthly and quarterly sales reports.
• Employee performance dashboards.
• Product pricing and inventory analysis.
• Department-wise expense reports.
• Student marksheets and examination results.
• Financial models and management reports.

2. Learning Objectives

• Understand the INDEX and MATCH functions.
• Learn how to perform row-wise and column-wise lookups.
• Build a two-way lookup formula using two MATCH functions.
• Retrieve values from large tables using dynamic cell references.
• Handle lookup errors with IFERROR.
• Use absolute references to make formulas reusable.
• Troubleshoot common lookup problems.
• Apply two-way lookups to practical business scenarios.

3. Understanding the INDEX Function

The INDEX function returns a value from a specified position within a range or array. In a two-dimensional table, you can specify both a row number and a column number.

Syntax

=INDEX(array, row_num, [column_num])

Example

Suppose cells A1:C3 contain the following data:

To retrieve the value in the second row and second column of the range A1:C3, use:

=INDEX(A1:C3,2,2)

Result: 1200

INDEX returns the value at the specified position within the selected range. The row and column numbers are relative to the beginning of that range, not necessarily the worksheet's row and column numbers.

4. Understanding the MATCH Function

The MATCH function searches for a specified value in a single row or column and returns its relative position. It identifies where a value is located rather than returning the value itself.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

Example

Suppose cells A2:A5 contain the following employee names:

To find the position of Amit, use:

=MATCH("Amit",A2:A5,0)

Result: 3

MATCH returns 3 because Amit is the third item in the selected range A2:A5, even though the value is located in worksheet cell A4.

For exact lookups, use 0 as the third argument to avoid relying on sorted data.

5. What Is a Two-Way Lookup Using INDEX and MATCH?

A two-way lookup combines INDEX with two MATCH functions. The first MATCH finds the required row, while the second MATCH finds the required column. INDEX then returns the value at the intersection of those positions.

General formula:

=INDEX(data_range, MATCH(row_lookup_value, row_lookup_range, 0), MATCH(column_lookup_value, column_lookup_range, 0))

The formula performs three main operations:

1. Find the position of the requested row label.
2. Find the position of the requested column heading.
3. Return the value at the intersection of the matching row and column.

This approach is more flexible than hardcoding a row number or column number because the positions are calculated automatically from the selected labels.

6. Example Dataset for a Two-Way Lookup

Consider the following monthly sales report for four employees:

Assume the dataset occupies cells A1:E5, with employee names in A2:A5, month headings in B1:E1, and sales values in B2:E5.

Suppose you want to find Priya's sales for March. The lookup must identify:

• Row: Priya in the employee list.
• Column: March in the month headings.
• Result: The sales value at the intersection of Priya's row and March's column.

7. Step-by-Step: Performing a Two-Way Lookup

Step 1: Identify the Data Range

The sales data is stored in B2:E5. This is the range from which INDEX will return the final result.

B2:E5

Step 2: Find the Matching Row

Use MATCH to find the relative position of Priya in A2:A5.

=MATCH("Priya",A2:A5,0)

Result: 2

Priya is the second employee in the selected range.

Step 3: Find the Matching Column

Use MATCH to find the relative position of March in B1:E1.

=MATCH("March",B1:E1,0)

Result: 3

March is the third month in the selected range.

Step 4: Combine INDEX and MATCH

Combine the two MATCH functions inside INDEX:

=INDEX(B2:E5,MATCH("Priya",A2:A5,0),MATCH("March",B1:E1,0))

Result: 29000

Excel locates the second row and third column of B2:E5 and returns the value 29000.

8. Using Cell References for Dynamic Lookups

Hardcoding employee names and months directly into a formula works for a single lookup, but cell references make the worksheet reusable.

Create the following lookup area:

Enter the following formula in G4:

=INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(G3,$B$1:$E$1,0))

When G2 contains Priya and G3 contains March, the formula returns 29000. Change G2 to Amit and G3 to April, and the result updates to 35000.

Advantages:

• One formula can handle many lookup requests.
• Users do not need to edit the formula for every search.
• Absolute references preserve the source ranges when copying formulas.
• The formula can be combined with dropdown lists to improve usability.

9. Using Absolute and Relative References

Correct cell referencing is important when formulas are copied or reused across worksheets.

For a two-way lookup formula, absolute references are often appropriate for the source data ranges:

=INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(G3,$B$1:$E$1,0))

The lookup inputs G2 and G3 remain relative references, while the data and header ranges are fixed.

10. Handling Missing Values with IFERROR

If MATCH cannot find a requested employee or month, it returns #N/A. You can use IFERROR to display a more useful message instead of showing an error in the report.

Formula:

=IFERROR(INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(G3,$B$1:$E$1,0)),"No Matching Data")

If the selected employee or month does not exist in the relevant lookup range, the formula displays No Matching Data.

IFERROR can also hide unrelated errors, so use it after validating that the formula and source ranges are correct. If you only want to handle missing lookup results, consider a more targeted error-handling approach when appropriate.

11. Creating Dropdown Lists for Two-Way Lookups

Data Validation dropdowns allow users to select an employee and month without manually typing their names. This reduces typing mistakes and makes the lookup sheet easier to use.

Creating the Employee Dropdown

1. Select cell G2.
2. Open the Data tab in Excel.
3. Select Data Validation.
4. Under Allow, choose List.
5. Enter the source range as =$A$2:$A$5.
6. Confirm the settings.

Creating the Month Dropdown

1. Select cell G3.
2. Open Data Validation and choose List.
3. Enter the source range as =$B$1:$E$1.
4. Confirm the settings.

Keep the two-way lookup formula in G4. Selecting different employees and months from the dropdowns will update the displayed result automatically.

12. Applying Two-Way Lookups to Product Pricing

Two-way lookups can retrieve prices when products are listed vertically and customer categories are arranged horizontally.

Assume the table occupies A1:D5. Product names are in A2:A5, customer categories are in B1:D1, and prices are in B2:D5.

To retrieve the wholesale price of the Monitor, use:

=INDEX(B2:D5,MATCH("Monitor",A2:A5,0),MATCH("Wholesale",B1:D1,0))

Result: 13000

This formula can be adapted to retrieve the price for any product and customer category in the table.

13. Applying Two-Way Lookups to Student Marks

Educational institutions can use two-way lookups to retrieve a student's marks in a selected subject.

Assuming the table occupies A1:D4, the formula to find Arjun's Science marks is:

=INDEX(B2:D4,MATCH("Arjun",A2:A4,0),MATCH("Science",B1:D1,0))

Result: 84

This approach makes it easy to build student performance reports without searching manually through multiple rows and columns.

14. Common Errors and Troubleshooting

15. Best Practices for Two-Way Lookups

• Use exact-match mode, represented by 0 in MATCH, for employee names, product names, month labels, and identifiers.
• Keep row labels and column headers consistent and easy to understand.
• Ensure that the INDEX data range and both MATCH ranges align correctly.
• Use absolute references for source ranges that should not move when copied.
• Avoid duplicate row or column labels when unique results are required.
• Use dropdown lists to reduce manual typing errors.
• Apply IFERROR or targeted error handling where a clear fallback message is useful.
• Test the formula with different row and column combinations.
• Keep raw data organized and avoid unnecessary merged cells in lookup tables.
• Use Excel Tables or structured references where they make the workbook easier to maintain.

16. INDEX and MATCH vs. VLOOKUP and XLOOKUP

INDEX and MATCH remain valuable for compatibility and flexible lookup designs. In newer versions of Excel, XLOOKUP and XMATCH provide additional lookup options and can simplify some formulas.

17. Practical Exercises

Exercise 1: Employee Sales Lookup

Create a table with employee names in the first column and monthly sales in the remaining columns. Write a formula to retrieve sales for a selected employee and month.

Expected formula pattern:

=INDEX(B2:E5,MATCH(G2,A2:A5,0),MATCH(G3,B1:E1,0))

Exercise 2: Product Price Lookup

Create a table of products and pricing categories. Retrieve the price for a selected product and category using two-way lookup logic.

Exercise 3: Student Marks Lookup

Build a marksheet with students in rows and subjects in columns. Retrieve the marks for a selected student and subject.

Exercise 4: Department Expense Analysis

Create a table with departments in rows and months in columns. Use INDEX and MATCH to retrieve the expense for a particular department during a selected month.

Exercise 5: Error Handling

Modify a two-way lookup formula so it displays a useful message if either lookup value cannot be found.

Expected formula pattern:

=IFERROR(INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(G3,$B$1:$E$1,0)),"No Matching Data")

18. Mini Project: Dynamic Sales Analysis Dashboard

Project objective: Create a simple Excel dashboard that displays sales for any selected employee and month.

Project Requirements

• Create a sales dataset containing employee names and monthly sales figures.
• Use consistent row labels and column headings.
• Create an employee dropdown list.
• Create a month dropdown list.
• Apply INDEX and two MATCH functions to retrieve the selected sales figure.
• Use IFERROR or appropriate error handling for missing selections.
• Format the result as a readable sales report.
• Test the dashboard with different employee and month combinations.

Expected Outcome

The finished worksheet should allow users to select an employee and month and immediately view the corresponding sales amount. This project demonstrates how lookup formulas can automate repetitive reporting tasks and support business analysis.

19. Interview Questions and Answers

Q1. What is a two-way lookup in Excel?

Answer: A two-way lookup retrieves a value by matching both a row label and a column heading, returning the value at their intersection.

Q2. What is the role of INDEX in a two-way lookup?

Answer: INDEX returns a value from a specified position within a selected range using row and column numbers.

Q3. What does MATCH return?

Answer: MATCH returns the relative position of a matching value within a specified row or column.

Q4. Why are two MATCH functions used?

Answer: One MATCH identifies the row position, while the other identifies the column position. INDEX uses both positions to retrieve the correct value.

Q5. Why is 0 used in MATCH?

Answer: Zero requests an exact match, which is useful for locating specific employee names, products, months, or other labels.

Q6. What is the difference between INDEX and MATCH?

Answer: INDEX returns a value from a specified position, whereas MATCH identifies the position of a lookup value.

Q7. What causes #N/A in a two-way lookup?

Answer: One of the MATCH functions may not find the requested row or column label. Incorrect ranges, inconsistent data, and extra spaces are common causes.

Q8. Why should absolute references be used?

Answer: Absolute references keep the source ranges fixed when formulas are copied or reused.

Q9. Can INDEX and MATCH look up values in any direction?

Answer: Yes. The lookup ranges can be placed independently of the return range, allowing flexible lookup arrangements.

Q10. How can you make a two-way lookup interactive?

Answer: Use Data Validation dropdowns for row and column selections and reference those cells in the INDEX and MATCH formula.

20. Quick Revision Checklist

• INDEX returns a value from a specified position in a range.
• MATCH returns the relative position of a lookup value.
• A two-way lookup uses two MATCH functions inside INDEX.
• The first MATCH identifies the row.
• The second MATCH identifies the column.
• Use 0 in MATCH for exact matching.
• Make sure all lookup ranges align with the INDEX data range.
• Use absolute references for fixed source ranges.
• Use dropdown lists to make lookup worksheets interactive.
• Use IFERROR or suitable error handling for missing lookup values.

21. Conclusion

Performing two-way lookups using INDEX and MATCH is an important Advanced Excel skill for retrieving data from tables with both row and column criteria. MATCH identifies the required positions, and INDEX returns the corresponding value.

By combining these functions with absolute references, Data Validation dropdowns, and error handling, you can build flexible spreadsheets for sales analysis, employee performance tracking, product pricing, student marksheets, and financial reporting.

Regular practice with different datasets will help you understand how to create accurate, reusable lookup formulas and work more efficiently with large Excel tables.

Learn More with JustAcademy

Explore the Advanced Excel Course to improve your formula skills, data analysis techniques, and spreadsheet productivity.

Register for a Course Demo to learn more about the training program.

Visit JustAcademy to explore additional courses and learning resources.